package org.csu.mypetstore.persistence;

import org.apache.ibatis.annotations.*;
import org.aspectj.weaver.ast.Or;
import org.csu.mypetstore.domain.Order;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface OrderMapper {
    @Select("SELECT * FROM orders WHERE userid=#{username};")
    List<Order> getOrdersByUsername(String username);
    @Select("select billaddr1 AS billAddress1,billaddr2 AS billAddress2,billcountry AS billCountry,billcity,shipcountry,billstate,billtofirstname,billtolastname,billzip,shipaddr1 AS shipAddress1,shipaddr2 AS shipAddress2,shipcity,shipcountry,shipstate,shiptofirstname,shiptolastname,shipzip,cardtype,courier,creditcard,exprdate AS expiryDate,locale,orderdate,ORDERS.ORDERID,TOTALPRICE,USERID AS username,orderstatus.status FROM ORDERS,orderstatus WHERE orders.orderId=#{orderId}\n" +
            "   AND orderstatus.orderid=#{orderId};")
    Order getOrder(int orderId);
    @Insert("INSERT INTO ORDERS VALUES(#{orderId}, #{username}, #{orderDate}, #{shipAddress1}, #{shipAddress2,jdbcType=VARCHAR}, #{shipCity},\n" +
            "      #{shipState}, #{shipZip}, #{shipCountry}, #{billAddress1}, #{billAddress2,jdbcType=VARCHAR}, #{billCity},\n" +
            "      #{billState}, #{billZip}, #{billCountry}, #{courier}, #{totalPrice}, #{billToFirstName}, #{billToLastName},\n" +
            "      #{shipToFirstName}, #{shipToLastName}, #{creditCard}, #{expiryDate}, #{cardType}, #{locale});")
    void insertOrder(Order order);
    @Insert("INSERT INTO ORDERSTATUS (ORDERID, LINENUM, TIMESTAMP, STATUS)\n" +
            "    VALUES (#{orderId,jdbcType=NUMERIC}, #{orderId,jdbcType=NUMERIC}, #{orderDate,jdbcType=TIMESTAMP}, #{status,jdbcType=VARCHAR})")
    void insertOrderStatus(Order order);
    @Select("select billaddr1 AS billAddress1,billaddr2 AS billAddress2,billcountry AS billCountry,billcity,shipcountry,billstate,billtofirstname,billtolastname,billzip,shipaddr1 AS shipAddress1,shipaddr2 AS shipAddress2,shipcity,shipcountry,shipstate,shiptofirstname,shiptolastname,shipzip,cardtype,courier,creditcard,exprdate AS expiryDate,locale,orderdate,ORDERS.ORDERID,TOTALPRICE,USERID AS username,orderstatus.status FROM ORDERS,orderstatus WHERE orderstatus.orderid=orders.orderId;")
    List<Order> getAllOrder();
    @Delete("delete from orders where orderid=#{orderid};")
    void deleteorder(String orderid);
    @Select("select billaddr1 AS billAddress1,billaddr2 AS billAddress2,billcity,shipcountry,billstate,billtofirstname,billtolastname,billzip,shipaddr1 AS shipAddress1,shipaddr2 AS shipAddress2,shipcity,shipcountry,shipstate,shiptofirstname,shiptolastname,shipzip,cardtype,courier,creditcard,exprdate AS expiryDate,locale,orderdate,ORDERS.ORDERID,TOTALPRICE,USERID AS username FROM ORDERS where orderid like #{keyword} or userid like #{keyword};")
    List<Order> searchorder(String keyword);
    @Update("UPDATE  orderstatus SET status=#{status} WHERE orderid=#{orderId};")
    void deliveryorder(@Param("orderId") String orderId, @Param("status") String status);


}
